package integrationTier.sql;

import core.MovieException;
import integrationTier.*;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import core.Genre;

/**
 * SQL DAO objekt k zanrum
 * @author vojta
 */
public class GenreSQLDAO implements IGenreDAO{

    Statement stmt;

    public GenreSQLDAO(Connection cn) throws MovieException {
        try {
            stmt = cn.createStatement();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Vypise vsechny zanry
     * @return ArrayList
     * @throws MovieException
     */
    public List<Genre> listAll() throws MovieException {
        List<Genre> list = new ArrayList<Genre>();
        String q = "SELECT * FROM \"genres\"";
        try {
            ResultSet rs = stmt.executeQuery(q);

            while (rs.next()) {
                list.add(new Genre(rs.getInt("id"), rs.getString("genre")));
            }            
            return list;
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }


    }

    /**
     * Ziska informace o danem zanru
     * @param genre
     * @return Genre
     * @throws MovieException
     */
    public Genre get(Genre genre) throws MovieException {
        try {
            int id = genre.getId();
            String q = "SELECT * FROM \"genres\" WHERE \"id\" = " + id + "";
            ResultSet rs = stmt.executeQuery(q);
            rs.next();            
            Genre g = new Genre(id, rs.getString("genre"));
            return g;
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }

    }

    /**
     * Zjisti, jestli je dany zanr pouzit
     * @param genre
     * @return boolean
     * @throws MovieException
     */
    private boolean isUsed(Genre genre) throws MovieException {
        try {
            String q = "SELECT * FROM \"movies\" WHERE \"genre_id\" = " + genre.getId() + "";
            ResultSet rs = stmt.executeQuery(q);
            rs.next();

            rs.getString("id");
            return true;
        } catch (SQLException ex) {
            return false;
        }

    }

    /**
     * Prida zanr
     * @param genre
     * @throws MovieException
     */
    public void add(Genre genre) throws MovieException {
        try {            
           String q = "INSERT INTO \"genres\"(\"genre\") VALUES('"+ genre.getGenre() +"')";
           stmt.executeUpdate(q);            
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }

    }

    /**
     * Odstrani zanr
     * @param genre
     * @throws MovieException
     */
    public void remove(Genre genre) throws MovieException {
        try {
            if (!isUsed(genre)) {
                String q = "DELETE FROM \"genres\" WHERE \"id\" = " + genre.getId() + "";
                stmt.executeUpdate(q);
            }
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Zmeni zanr
     * @param genre
     * @throws MovieException
     */
    public void change(Genre genre) throws MovieException {
        try {
            String q = "UPDATE \"genres\" SET \"genre\" = '" + genre.getGenre() + "' WHERE \"id\" = " + genre.getId() + "";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Ukonci pripojeni k DB
     * @throws MovieException
     */
    public void close() throws MovieException {
        try {
            stmt.execute("SHUTDOWN");
            stmt.close();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

}
